﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class Sponsor, dependiente de la clase Person, define una persona natural
/// </summary>
//tratamiento especial para AGE
#region Class

public class clsSponsor : DAC {
    #region Variables

    private int idSponsor, idPLegal, iPosition;
    private String sName, sUrlImage, dtDate, sTitle;    
    private const String tabla = "dbo.tblSponsor";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsSponsor() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".idSponsor As ID, " + tabla + ".idPLegal As LEGAL, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".iPosition As POSITION, ";
        query += tabla + ".sTitle As TITLE ";
        query += "FROM " + tabla;
        return this.ExecuteQuery(query);
    }

    public DataSet Sponsor1() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".sTitle As TITLE ";
        query += " FROM " + tabla;
        query += " WHERE " + tabla + ".iPosition=1";
        return this.ExecuteQuery(query);
    }
    public DataSet Sponsor2() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".sTitle As TITLE ";
        query += " FROM " + tabla;
        query += " WHERE " + tabla + ".iPosition=2";
        return this.ExecuteQuery(query);
    }
    public DataSet Sponsor3() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".sTitle As TITLE ";
        query += " FROM " + tabla;
        query += " WHERE " + tabla + ".iPosition=3";
        return this.ExecuteQuery(query);
    }
    public DataSet Sponsor4() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".sTitle As TITLE ";
        query += " FROM " + tabla;
        query += " WHERE " + tabla + ".iPosition=4";
        return this.ExecuteQuery(query);
    }
    public DataSet Sponsor5() {
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".sTitle As TITLE ";
        query += " FROM " + tabla;
        query += " WHERE " + tabla + ".iPosition=5";
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text){
        String query = "SELECT row_number() over(order by " + tabla + ".idSponsor) As NUMBER, ";
        query += tabla + ".idSponsor As ID, " + tabla + ".idPLegal As LEGAL, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".iPosition As POSITION, ";
        query += tabla + ".sTitle As TITLE ";
        query += "FROM " + tabla;
        if(!text.Equals("")){
            query += " WHERE " + tabla + ".sName LIKE '%" + text + "%' ";
        }
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet ConsultID(String id) {
        String query = "SELECT ";
        query += tabla + ".idSponsor As ID, " + tabla + ".idPLegal As LEGAL, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".iPosition As POSITION, ";
        query += tabla + ".sTitle As TITLE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idSponsor =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultPLegalID(String id) {
        String query = "SELECT ";
        query += tabla + ".idSponsor As ID, " + tabla + ".idPLegal As LEGAL, ";
        query += tabla + ".sName As NAME, " + tabla + ".sUrlImage As URLImage, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".iPosition As POSITION, ";
        query += tabla + ".sTitle As TITLE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idPLegal=" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert() {
        String query = "INSERT INTO " + tabla;
        query += "(idPLegal, sName, sUrlImage, dtDate, iPosition, sTitle) ";
        query += "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') SELECT CAST(scope_identity() AS int)";

        String sql = String.Format(query,this.IdPLegal, this.SName, this.SUrlImage, this.DtDate, this.IPosition, this.STitle);
        return this.ExecuteDML(sql, "no");
    }

    public int Update(){
        String query = "UPDATE " + tabla + " SET ";        
        query += tabla + ".idPLegal=" + this.IdPLegal + ", " + tabla + ".sName='" + this.SName + "', ";
        query += tabla + ".sUrlImage='" + this.SUrlImage + "', " + tabla + ".dtDate='" + this.DtDate + "', ";
        query += tabla + ".iPosition='" + this.IPosition + "', " + tabla + ".sTitle='" + this.STitle + "' ";
        query += "WHERE " + tabla + ".idSponsor=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idSponsor={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    
    public int ID
    {
        get { return idSponsor; }
        set { idSponsor = value; }
    }

    public int IdPLegal {
        get { return idPLegal; }
        set { idPLegal = value; }
    }
    
    public String SName
    {
        get { return sName; }
        set { sName = value; }
    }
    
    public String DtDate {
        get { return dtDate; }
        set { dtDate = value; }
    }

    public String SUrlImage {
        get { return sUrlImage; }
        set { sUrlImage = value; }
    }

    #endregion

    public int IPosition {
        get { return iPosition; }
        set { iPosition = value; }
    }
    public String STitle {
        get { return sTitle; }
        set { sTitle = value; }
    }
}

#endregion